package cn.yunhe.Dao.impl;

import cn.yunhe.Dao.IEmpDao;
import cn.yunhe.entity.Dept;
import cn.yunhe.entity.Emp;
import lombok.Getter;
import lombok.Setter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Component
@Getter
@Setter
public class EmpDao implements IEmpDao {//导错包了
    public static final RowMapper rowMapper = new ParameterizedRowMapper() {
        @Override
        public Object mapRow(ResultSet rs, int i) throws SQLException {
            Emp emp = new Emp();
            emp.setEmpno(rs.getInt("empno"));
            emp.setEname(rs.getString("ename"));
            emp.setJob(rs.getString("job"));
            Dept dept = new Dept();
            dept.setDeptno(rs.getInt("deptno"));
            dept.setDname(rs.getString("dname"));
            dept.setLoc(rs.getString("loc"));
            emp.setDept(dept);
            return emp;
        }
    };

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Emp> getAllEmp() {
        String sql="select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc from " +
                "emp e inner join dept d on e.deptno=d.deptno";
       List<Emp> list=jdbcTemplate.query(sql,rowMapper);
        return list;
    }

    @Override
    //模糊查询
    public List<Emp> getEmpByPro(Emp emp) {
        StringBuffer sql=new StringBuffer("select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc from emp e inner join dept d on e.deptno=d.deptno where 1=1");
        List list=new ArrayList();
        if (emp!=null){
            if(emp.getEname()!=null&&emp.getEname().trim().length()!=0){
                sql.append("and e.ename like ?");
                list.add("%"+emp.getEname()+"%");
            }
            if (emp.getDept()!=null&&emp.getDept().getDeptno()>0){
                sql.append("and d.deptno=?");
                list.add(emp.getDept().getDeptno());
            }

        }
        Object[] params=list.toArray();
        return jdbcTemplate.query(sql.toString(),params,rowMapper);
    }

    @Override
    public Emp getEmpById(int empno) {
        String sql="select e.empno,e.ename,e.job,d.deptno,d.dname,d.loc from emp e inner join dept d on e.deptno=d.deptno where e.empno=?";
       Object[] params={empno};
       List<Emp> list=jdbcTemplate.query(sql,params,rowMapper);
        return list.size()>0?list.get(0):null;
    }

    @Override
    public int saveEmp(Emp emp) {
        String sql="insert into emp(empno,ename,job) values(?,?,?)";
        Object[] params={emp.getEmpno(),emp.getEname(),emp.getJob()};
        return jdbcTemplate.update(sql,params);
    }

    @Override
    public int delEmp(int empno) {
        int result = 0;
        String sql="delete from emp where empno = ?";
        Object[] params={empno};
        result= jdbcTemplate.update(sql,params);
        return  result;
    }

    @Override
    public int updateEmp(Emp emp) {
        String sql="update emp set ename=? where empno=?";
        Object[] params={emp.getEname(),emp.getEmpno()};
        return jdbcTemplate.update(sql,params);
    }
}
